#!/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2020/12/9 10:51
# @Author  : LiShan
# @Email   : lishan_1997@126.com
# @File    : interface.py
# @Note    : this is note

import random
import sys
import threading  # 用于多线程异步处理               数据，防止卡顿
from functools import wraps  # 用于装饰器信息修正

import pymysql  # 用于Mysql数据库操作
from PyQt5.QtCore import *
from PyQt5.QtGui import *
from PyQt5.QtWidgets import *
from PyQt5.uic import loadUiType  # 用于动态加载UI
from matplotlib.backends.backend_qt5agg import FigureCanvasQTAgg as FigureCanvas
from matplotlib.figure import Figure

interface_ui, _ = loadUiType("interface.ui")


def delay_seconds_clean_status_bar(time_duration):
    def decorator(func):
        @wraps(func)
        def wrappered_func(window_widget):
            def cleaner():
                window_widget.statusBar().showMessage("")
            ret_value = func(window_widget)
            timer = threading.Timer(time_duration, cleaner)  # 异步延迟，防止GUI密集操作造成的卡顿
            timer.start()
            return ret_value
        return wrappered_func
    return decorator


class PlotCanvas(FigureCanvas):
    """
    通过继承FigureCanvas类，使得该类既是一个PyQt5的Qwidget，
    又是一个matplotlib的FigureCanvas，这是连接pyqt5与matplotlib的关键
    """
    def __init__(self, parent=None, width=8, height=6, dpi=100):
        fig = Figure(figsize=(width, height), dpi=dpi)
        FigureCanvas.__init__(self, fig)
        self.setParent(parent)
        FigureCanvas.setSizePolicy(self, QSizePolicy.Expanding, QSizePolicy.Expanding)
        FigureCanvas.updateGeometry(self)

    def plot(self, road=None, coordinate=None):
        # 默认四条道路,每条道路的路况拥挤程度
        if road is None:
            road = [0, 1, 2, 3]
        # 默认每条道路的起点x、终点x坐标、起点y、终点y坐标
        if coordinate is None:
            coordinate = [
                [10, 40, 20, 20],
                [10, 40, 10, 10],
                [20, 20,  0, 30],
                [30, 30,  0, 30],
            ]
        # 路况等级颜色,不同等级用不同的颜色标记
        condition_colour = [
            "g-",       # 绿色：畅通
            "y-",       # 黄色：轻微拥堵
            "r-",       # 红色：拥堵
            "#a0522d",  # 褐色：严重拥堵
        ]
        ax = self.figure.add_subplot(111)
        # 关闭坐标轴
        ax.axis('off')
        for i in range(len(road)):
            ax.plot(coordinate[i][0:2], coordinate[i][2:4], condition_colour[road[i]], linewidth=4)
        # ax.set_title('map')


class MainWindow(QMainWindow, interface_ui):
    def __init__(self):
        QMainWindow.__init__(self)
        self.setupUi(self)  # 该方法派生自UI类
        self.m_flag = False
        self.m_Position = None
        self.handle_ui_changes()
        self.handle_buttons()
        self.setWindowTitle("城市道路智慧交通管理系统")
        self.setWindowIcon(QIcon("timgg.jpg"))
        self.setFixedSize(self.width(), self.height())  # 禁止拉伸窗口大小
        self.show_signal_time()
        # self.setStyleSheet("background-image: url(timg.jpg)")  # 设置窗口背景图片
        # self.setStyleSheet("background-image: url(image.jpg)")  # 设置窗口背景图片
        # self.setAttribute(QtCore.Qt.WA_TranslucentBackground)  # 设置窗口背景透明
        self.road_data = []
        self.coordinate_data = []
        self.timer = QTimer()
        self.switch_to_map_panel()

    def mousePressEvent(self, event):
        if event.button() == Qt.LeftButton:
            self.m_flag = True
            self.m_Position = event.globalPos() - self.pos()  # 获取鼠标相对窗口的位置
            event.accept()
            self.setCursor(QCursor(Qt.OpenHandCursor))  # 更改鼠标图标

    def mouseMoveEvent(self, QMouseEvent):
        if Qt.LeftButton and self.m_flag:
            self.move(QMouseEvent.globalPos() - self.m_Position)  # 更改窗口位置
            QMouseEvent.accept()

    def mouseReleaseEvent(self, QMouseEvent):
        self.m_flag = False
        self.setCursor(QCursor(Qt.ArrowCursor))  # 在释放鼠标按键之后恢复为箭头形状的图标

    def handle_buttons(self):
        # 主界面界面切换
        self.pushButton1.clicked.connect(self.switch_to_map_panel)  # 按下按钮1切换到地图界面
        self.pushButton2.clicked.connect(self.switch_to_traffic_state_panel)  # 按下按钮2切换到交通状态界面
        self.pushButton3.clicked.connect(self.switch_to_job_state_panel)  # 按下按钮3切换到工作状态界面
        self.pushButton2_2.clicked.connect(self.switch_show_job_time_panel)  # 按下按钮5切换到信号灯配时界面
        # 查找功能
        self.pushButton4.clicked.connect(self.show_traffic_state)  # 按下按钮4切换到交通状态查询
        self.pushButton4_2.clicked.connect(self.show_job_state)  # 按下按钮5切换到工作状态查询

    def handle_ui_changes(self):
        self.tabWidget.tabBar().setVisible(False)  # 隐藏tab标签

    def refresh_map(self):
        dr = PlotCanvas()
        # 实例化一个FigureCanvas
        dr.plot(self.road_data, self.coordinate_data)  # 画图
        graphic_scene = QGraphicsScene()
        # 第三步，创建一个QGraphicsScene
        graphic_scene.addWidget(dr)
        # 第四步，把图形放到QGraphicsScene中
        self.graphicsView.setScene(graphic_scene)
        # 第五步，把QGraphicsScene放入QGraphicsView
        self.graphicsView.show()
        # 最后，调用show方法呈现图形

    def update_data(self):
        self.coordinate_data = [
            [10, 40, 20, 20],
            [10, 40, 10, 10],
            [20, 20, 0, 30],
            [30, 30, 0, 30],
        ]
        self.road_data = []
        # data = [50,62,45,9,81,2,25,41]
        for i in range(len(self.coordinate_data)):
            # if 20<data[i]<50:
            #     self.road_data.append(0)
            # elif 50<=data[i]<70:
            #     self.road_data.append(1)
            # elif 70<=data[i]<80:
            #     self.road_data.append(2)
            # elif 80<=data[i]:
            self.road_data.append(random.randint(0, 3))
        self.textBrowser.clear()
        for i in range(len(self.road_data)):
            self.textBrowser.append("道路 " + str(i) + " 拥堵等级: " + str(self.road_data[i]))

    # 切换到电子地图界面
    def switch_to_map_panel(self):
        self.tabWidget.setCurrentIndex(0)
        self.showTime()
        self.timer.start(1000)
        self.timer.timeout.connect(self.showTime)

    def showTime(self):
        time = QDateTime.currentDateTime()
        timeDisplay = time.toString("yyyy-MM-dd hh:mm:ss dddd")
        self.label_time.setText(timeDisplay)
        self.update_data()
        self.refresh_map()

    # 切换到信号灯配时界面
    def switch_show_job_time_panel(self):
        self.tabWidget.setCurrentIndex(1)
        print("配时界面")
        # self.timer.stop()

    # 切换界面到交通状态界面
    def switch_to_traffic_state_panel(self):
        self.tabWidget.setCurrentIndex(2)
        print("交通状态")
        self.timer.stop()

    # 切换界面到工作状态界面
    def switch_to_job_state_panel(self):
        self.tabWidget.setCurrentIndex(3)
        print("工作状态")
        # self.timer.stop()

    # 电子地图界面
    @delay_seconds_clean_status_bar(5)
    def show_map_state(self):
        print("来到电子地图界面")

    # 交通状态界面：查询对应信息的数据
    @delay_seconds_clean_status_bar(5)
    def show_traffic_state(self):
        # 首先清空查询结果表
        self.tableWidget.setRowCount(0)
        self.tableWidget.clearContents()
        # connect to the database with the host \ username \ password \ schema name
        self.db = pymysql.connect(host='127.0.0.1', user='root', password='lucky123', db='ren_project', port=3306, charset='utf8')
        self.cur = self.db.cursor()
        date = self.comboBox1_2.currentText()
        date_time = self.comboBox1_5.currentText()
        area = self.comboBox1.currentText()
        cross = self.comboBox2.currentText()
        phase = self.comboBox2_2.currentText()

        if date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口1" and phase == "相位1":
            area = 1
            cross = 1
            phase = 1
            self.cur.execute(
            "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
            "FROM state,traffic_501,length_501,delay_501 "
            "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口1" and phase == "相位2":
            area = 1
            cross = 1
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))

        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口1" and phase == "相位3":
            area = 1
            cross = 1
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口1" and phase == "相位4":
            area = 1
            cross = 1
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口2" and phase == "相位1":
            area = 1
            cross = 2
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口2" and phase == "相位2":
            area = 1
            cross = 2
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口2" and phase == "相位3":
            area = 1
            cross = 2
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口2" and phase == "相位4":
            area = 1
            cross = 2
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口3" and phase == "相位1":
            area = 1
            cross = 3
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口3" and phase == "相位2":
            area = 1
            cross = 3
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口3" and phase == "相位3":
            area = 1
            cross = 3
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口3" and phase == "相位4":
            area = 1
            cross = 3
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口4" and phase == "相位1":
            area = 1
            cross = 4
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口4" and phase == "相位2":
            area = 1
            cross = 4
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口4" and phase == "相位3":
            area = 1
            cross = 4
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口4" and phase == "相位4":
            area = 1
            cross = 4
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口1" and phase == "相位1":
            area = 2
            cross = 1
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口1" and phase == "相位2":
            area = 2
            cross = 1
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口1" and phase == "相位3":
            area = 2
            cross = 1
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口1" and phase == "相位4":
            area = 2
            cross = 1
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口2" and phase == "相位1":
            area = 2
            cross = 2
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口2" and phase == "相位2":
            area = 2
            cross = 2
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口2" and phase == "相位3":
            area = 2
            cross = 2
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口2" and phase == "相位4":
            area = 2
            cross = 2
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口3" and phase == "相位1":
            area = 2
            cross = 3
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口3" and phase == "相位2":
            area = 2
            cross = 3
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口3" and phase == "相位3":
            area = 2
            cross = 3
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路3" and phase == "相位4":
            area = 2
            cross = 3
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口4" and phase == "相位1":
            area = 2
            cross = 4
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口4" and phase == "相位2":
            area = 2
            cross = 4
            phase = 2
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口4" and phase == "相位3":
            area = 2
            cross = 4
            phase = 3
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))
        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域2" and cross == "路口4" and phase == "相位4":
            area = 2
            cross = 4
            phase = 4
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,traffic_501.one,length_501.one,delay_501.one "
                "FROM state,traffic_501,length_501,delay_501 "
                "WHERE state.id = traffic_501.id and state.id = length_501.id and state.id = delay_501.id and areaid=%s and crossid=%s and phaseid=%s;",
                (area, cross, phase))


        if date == "2020年5月2日":
            if date_time == "8:00":
                self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,traffic_502.one,length_502.one,delay_502.one "
                    "FROM state,traffic_502,length_502,delay_502 "
                    "WHERE state.id = traffic_502.id and state.id = length_502.id and state.id = delay_502.id;")
            elif date_time == "8:10":
                self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,traffic_502.two,length_502.two,delay_502.two "
                    "FROM state,traffic_502,length_502,delay_502 "
                    "WHERE state.id = traffic_502.id and state.id = length_502.id and state.id = delay_502.id;")
            elif date_time == "8:20":
                self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,traffic_502.three,length_502.three,delay_502.three "
                    "FROM state,traffic_502,length_502,delay_502 "
                    "WHERE state.id = traffic_502.id and state.id = length_502.id and state.id = delay_502.id;")
        elif date == "2020年5月3日":
            if date_time == "8:00":
                self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,traffic_503.one,length_503.one,delay_503.one "
                    "FROM state,traffic_503,length_503,delay_503 "
                    "WHERE state.id = traffic_503.id and state.id = length_503.id and state.id = delay_503.id;")
            elif date_time == "8:10":
                self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,traffic_503.two,length_503.two,delay_503.two "
                    "FROM state,traffic_503,length_503,delay_503 "
                    "WHERE state.id = traffic_503.id and state.id = length_503.id and state.id = delay_503.id;")
            elif date_time =="8:20":
                self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,traffic_503.three,length_503.three,delay_503.three "
                    "FROM state,traffic_503,length_503,delay_503 "
                    "WHERE state.id = traffic_503.id and state.id = length_503.id and state.id = delay_503.id;")
        data = self.cur.fetchall()
        self.db.commit()
        self.tableWidget.setRowCount(0)  # 从头开始绘制
        self.tableWidget.insertRow(0)  # 首先插入一个空行
        row_position = 1
        for row, line in enumerate(data):
            for column, element in enumerate(line):
                self.tableWidget.setItem(row, column, QTableWidgetItem(str(element)))
            self.tableWidget.insertRow(row_position)
            row_position = row_position + 1
        # 状态栏输出结果
        result = "查询完成 共查询到 {0} 条结果".format(len(data))
        self.statusBar().showMessage(" " * 5 + result)
        self.db.close()
        return

    # 工作状态界面
    @delay_seconds_clean_status_bar(5)
    def show_job_state(self):
        # 首先清空查询结果表
        self.tableWidget_2.setRowCount(0)
        self.tableWidget_2.clearContents()
        # connect to the database with the host \ username \ password \ schema name
        self.db = pymysql.connect(host='127.0.0.1', user='root', password='lucky123', db='ren_project', port=3306, charset='utf8')
        self.cur = self.db.cursor()
        date = self.comboBox1_3.currentText()
        date_time = self.comboBox1_6.currentText()
        area = self.comboBox1_4.currentText()
        cross = self.comboBox2_3.currentText()
        phase = self.comboBox2_4.currentText()

        if date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口1" and phase == "相位1":
            area = 1
            cross = 1
            phase = 1
            self.cur.execute(
                    "SELECT state.areaid,state.crossid,state.phaseid,jobstatus_501.one FROM state,jobstatus_501 "
                    "WHERE state.id = jobstatus_501.id and areaid=%s and crossid=%s and phaseid=%s;", (area, cross, phase))

        elif date == "2020年5月1日" and date_time == "8:00" and area == "区域1" and cross == "路口2" and phase == "相位1":
            area = 1
            cross = 2
            phase = 1
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,jobstatus_501.one FROM state,jobstatus_501 "
                "WHERE state.id = jobstatus_501.id and areaid=%s and crossid=%s and phaseid=%s;", (area, cross, phase))

        elif date == "2020年5月2日" and date_time == "8:00":
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,jobstatus_501.three FROM state,jobstatus_501 WHERE state.id = jobstatus_501.id;")
        elif date == "2020年5月2日" and date_time == "8:03":
            self.cur.execute(
                "SELECT state.areaid,state.crossid,state.phaseid,jobstatus_501.one FROM state,jobstatus_501 WHERE state.id = jobstatus_501.id;")
        data = self.cur.fetchall()
        self.db.commit()
        self.tableWidget_2.setRowCount(0)  # 从头开始绘制
        self.tableWidget_2.insertRow(0)  # 首先插入一个空行
        row_position = 1
        for row, line in enumerate(data):
            # print("in row {0}".format(row))
            for column, element in enumerate(line):
                # print("in row {0} col {1}".format(row, column))
                # if element is not None:
                #     print(element, type(element))
                self.tableWidget_2.setItem(row, column, QTableWidgetItem(str(element)))
            self.tableWidget_2.insertRow(row_position)
            row_position = row_position + 1
        # 状态栏输出结果
        result = "查询完成 共查询到 {0} 条结果".format(len(data))
        self.statusBar().showMessage(" " * 5 + result)
        # 关闭数据库连接，结束查询
        self.db.close()
        # 该分支逻辑结束，退出
        return

    # 信号灯配时界面
    def show_signal_time(self):
        # self.label_18.setText("15")
        # a = self.label_18.text()
        # self.time.start()
        # self.time = QTimer(self.label_18)
        # self.time.setInterval(1000)
        # if a > 0:
        #     self.label_18.setText(str(a))
        #     a -= 1
        # else:
        #     self.time.stop()

        # for i in len(int(self.label_18.text())):
        #     self.id = self.label_18.startTimer(1000)
        #     a = int(self.label_18.text()) - 1
        #     self.label_18.setText(str(a))
        #     print(a)
        #     if a == 0:
        #         print(a)
        #         self.killTimer(self.id)

        class Label(QLabel):
            def __init__(self, par):
                super().__init__(par)
                self.setText('10')
                self.d = par

            def timerEvent(self, evt):
                a = int(self.text()) - 1
                if a == 0:
                    self.killTimer(self.d.id)
                self.setText(str(a))


if __name__ == "__main__":
    app = QApplication(sys.argv)  # 创建一个QApplication，也就是你要开发的软件app
    window = MainWindow()  # MainWindow()类的实例化对象
    window.show()  # 执行QMainWindow的show()方法，显示这个QMainWindow
    sys.exit(app.exec_())  # 使用exit()或者点击关闭按钮退出QApplication
